package com.kevin.table;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.commons.lang3.StringUtils;

import com.delete.sql.SqlCreate;
import com.kevin.enums.DBType;
import com.kevin.sql.DataType;
import com.kevin.sql.SqlFormat;
import com.kevin.utils.StaticTool;
import com.kevin.utils.common.KnCommonUtils;

public class MTableFilter {
	public static MDataTable[] split(MDataTable table, Object whereObj)
    {
        MDataTable[] mdt2 = new MDataTable[2];
        mdt2[0] = table.getSchema(false);
        mdt2[1] = table.getSchema(false);
        if (table.getRows().size() > 0)
        {
            if (whereObj.toString().trim().equals(StringUtils.EMPTY))
            {
                mdt2[0] = table;
            }
            else
            {

                List<TFilter> filters = getTFilter(whereObj, table.getColumns());
                if (filters.size() > 0)
                {
                    for (int i = 0; i < table.getRows().size(); i++)
                    {
                        MDataRow row = table.getRows().get(i);
                        if (compareMore(row, filters))
                        {
                            mdt2[0].getRows().add(row, false);
                        }
                        else
                        {
                            mdt2[1].getRows().add(row, false);
                        }
                    }
                }
            }
        }
        return mdt2;
    }
	
    public static int getCount(MDataTable table, Object whereObj)
    {
        int count = 0;
        if (table.getRows().size() > 0)
        {
            if (String.valueOf(whereObj).trim().equals(StringUtils.EMPTY))
            {
                return table.getRows().size();
            }
            List<TFilter> filters = getTFilter(whereObj, table.getColumns());
            if (filters.size() > 0)
            {
                for (int i = 0; i < table.getRows().size(); i++)
                {
                    if (compareMore(table.getRows().get(i), filters))
                    {
                        count++;
                    }
                }
            }
            filters = null;
        }
        return count;
    }

    public static int getIndex(MDataTable table, Object whereObj)
    {
        int index = -1;
        if (table.getRows().size() > 0)
        {
            if (String.valueOf(whereObj).trim() == "")
            {
                return 0;
            }
            List<TFilter> filters = getTFilter(whereObj, table.getColumns());
            if (filters.size() > 0)
            {
                for (int i = 0; i < table.getRows().size(); i++)
                {
                    if (compareMore(table.getRows().get(i), filters))
                    {
                        index = i;
                        break;
                    }
                }
            }
            filters = null;
        }
        return index;
    }

    public static MDataRowCollection findAll(MDataTable table, Object whereObj)
    {
        if (table != null && table.getRows().size() > 0)
        {
            MDataRowCollection findRows = new MDataRowCollection();
            String where = String.valueOf(whereObj).trim();
            if (where == "" || where == "1=1")
            {
            	findRows.addAll(table.getRows());
                return findRows;
            }
            String whereStr = SqlFormat.getFieldRowSql(whereObj);
            String orderby = StringUtils.EMPTY;
            String[] whereOrderby = splitWhereOrderby(whereStr, orderby);
            whereStr = whereOrderby[0];
            orderby = whereOrderby[1];
            List<TFilter> filters = getTFilter(whereStr, table.getColumns());
            List<MDataRow> rows = table.getRows();
            if (filters.size() > 0)
            {        
            	rows.clear();
            	for (MDataRow row : table.getRows()) {
					if(compareMore(row, filters)){
						rows.add(row);
					}
				}
            }
            
            findRows.addAll(rows);//添加找到的行。
            filters = null;
            if (StringUtils.isNotEmpty(orderby) && rows.size() > 1)//进行数组排序
            {
                findRows.sort(orderby);
                //MDataRowCollection sortRows = new MDataRowCollection();
                //sortRows.AddRange(getRows());
                //sortRows.Sort(orderby);
                //return sortRows;
            }
            return findRows;
        }
        return null;
    }

    public static MDataRow findRow(MDataTable table, Object whereObj)
    {
        if (table.getRows().size() > 0)
        {
            if (String.valueOf(whereObj).trim().equals(""))
            {
                return table.getRows().get(0);
            }
            String whereStr = SqlFormat.getFieldRowSql(whereObj);
            String orderby = StringUtils.EMPTY;
            String[] whereOrderby = splitWhereOrderby(whereStr, orderby);
            whereStr = whereOrderby[0];
            orderby = whereOrderby[1];
            MDataRowCollection sortRows = null;
            if (StringUtils.isNotEmpty(orderby) && table.getRows().size() > 1)//进行数组排序
            {
                sortRows = new MDataRowCollection();
                sortRows.addAll(table.getRows());
                sortRows.sort(orderby);
            }
            List<TFilter> filters = getTFilter(whereStr, table.getColumns());

            if (filters.size() > 0)
            {
                if (sortRows == null)
                {
                    sortRows = table.getRows();
                }
                for (int i = 0; i < sortRows.size(); i++)
                {
                    if (compareMore(sortRows.get(i), filters))
                    {
                        return sortRows.get(i);
                    }
                }
            }

        }
        return null;
    }

    public static MDataTable select(MDataTable table, int pageIndex, int pageSize, Object whereObj, Object... selectColumns)
    {
        if (table == null)
        {
            return null;
        }
        MDataTable sTable = table.getSchema(true);
        //sTable.RecordsAffected = table.Rows.Count;
        if (table.getRows().size() == 0)// 正常情况下，看是否需要处理列移除
        {
        	sTable = filterColumns(sTable, selectColumns);//列查询过滤
            return sTable;
        }
        MDataRowCollection findRows = findAll(table, whereObj);
        if (findRows != null)
        {
            //sTable.RecordsAffected = findRows.Count;//设置记录总数
            filterPager(findRows, pageIndex, pageSize);//进行分页筛选，再克隆最后的数据。

            for (int i = 0; i < findRows.size(); i++)
            {
                if (i < findRows.size())//内存表时（表有可能在其它线程被清空）
                {
                    MDataRow row = findRows.get(i);
                    if (row == null)
                    {
                        break;
                    }
                    sTable.createRow(true).loadFrom(row);
                }
            }

            findRows = null;

        }
        if (selectColumns != null && selectColumns.length > 0)
        {
        	sTable = filterColumns(sTable, selectColumns);//列查询过滤，由于查询的条件可能包含被移除列，所以只能在最后才过滤
        }
        //进行条件查询
        return sTable;
    }

    
    
    private static MDataTable filterColumns(MDataTable table, Object... selectColumns)
    {
        if (selectColumns != null && selectColumns.length > 0)
        {
            /// 列移除
            boolean contain = false;
            for (int i = 0; i < table.getColumns().size(); i++)
            {
                contain = false;
                for (Object columnName : selectColumns)
                {
                    String[] items = columnName.toString().split(StringUtils.SPACE);//a as b
                    if (table.getColumns().get(i).getColumnName().compareToIgnoreCase(items[0]) == 0)
                    {
                        contain = true;
                        if (items.length > 1)
                        {
                            table.getColumns().get(i).setColumnName(items[items.length - 1]);//修改列名
                        }
                        break;
                    }
                }
                if (!contain)
                {
                    table.getColumns().remove(i);
                    i--;
                }
            }
        }
        return table;
    }
    
	private static int indexOf(String where, String andOrSign, int start)
    {
        //为了处理如下的特殊情况："B like '%'' and ''%' and A='a' or A<2 order by a,b desc"); 第1个 and 是假的
        int index = where.indexOf(andOrSign, start);
        int lastIndex = start;
        while (index > -1)//前置的引号检查,如果引号为单，则继续取下一个
        {
            if (where.substring(start, index - start).split("\'").length % 2 == 0)//为双的话仅有单个的符号,将继续往后截
            {
                lastIndex = index + andOrSign.length();
                index = where.indexOf(andOrSign, lastIndex + 1);
            }
            else
            {
                return index;
            }
        }

        return index;
    }
    
    @SuppressWarnings({ "unchecked", "serial" })
	private static Map<String,Op> ops = new CaseInsensitiveMap(){
    	{
            put("<>", Op.NotEqual);
            put(">=", Op.BigEqual);
            put("<=", Op.SmallEqual);
            put("=", Op.Equal);
            put(">", Op.Big);
            put("<", Op.Small);
            put(" not like ", Op.NotLike);
            put(" like ", Op.Like);
            put(" is null", Op.IsNull);
            put(" is not null", Op.IsNotNull);
            put(" not in", Op.NotIn);//not int 顺序要靠前。
            put(" in", Op.In);
    	}
    };
    
    
    
    private static TFilter getSingleTFilter(String where, MDataColumn mdc)
    {
        //id like 'a>b=c'
        //id>'a like b'
        where = StringUtils.stripEnd(StringUtils.stripStart(where, "("), ")").trim();
        int quoteIndex = where.indexOf('\'');
        quoteIndex = quoteIndex == -1 ? 0 : quoteIndex;
        TFilter tFilter = null;
        for (Map.Entry<String, Op> opItem : ops.entrySet())
        {
        	tFilter = getTFilterOk(where, quoteIndex, opItem.getKey(), opItem.getValue(), mdc);
        	if(tFilter != null) break;
        }
        return tFilter;
    }
    
    private static TFilter getTFilterOk(String where, int quoteIndex, String sign, Op op, MDataColumn mdc)
    {
        TFilter tFilter = null;
        int index =where.toLowerCase().substring(0, quoteIndex > 0 ? quoteIndex : where.length()).indexOf(sign);
        if (index > 0)
        {
            String columnAName = where.substring(0, index).trim();
            int columnAIndex = mdc.indexOf(columnAName);
            String valueB = StringUtils.strip(where.substring(index + sign.length()), " \'").replace("''", "'");
            if (op == Op.In || op == Op.NotIn)
            {
                valueB = ',' + StringUtils.stripStart(valueB, "()").replace("'", "") + ",";//去除单引号。
            }
            int columnBIndex = -1;
            if (quoteIndex == 0 && mdc.contains(valueB)) //判断右侧的是否列名。
            {
                columnBIndex = mdc.indexOf(valueB);
            }
            tFilter = new TFilter(Ao.None, columnAName, columnAIndex, op, valueB, columnBIndex);
            if (columnBIndex == -1 && StringUtils.isNotEmpty(String.valueOf(valueB)))//右侧是值类型，转换值的类型。
            {
                if (columnAIndex > -1 && DataType.getGroup(mdc.get(columnAIndex).getSqlType()) == 3)//bool型
                {
                    switch (String.valueOf(tFilter.valueB).toLowerCase())
                    {
                        case "true":
                        case "1":
                        case "on":
                            tFilter.valueB = true;
                            break;
                        case "false":
                        case "0":
                        case "":
                            tFilter.valueB = false;
                            break;
                        default:
                            tFilter.valueB = null;
                            break;
                    }
                }
                else
                {
                    try
                    {
                        tFilter.valueB = StaticTool.changeType(tFilter.valueB, columnAIndex > -1 ? String.class : mdc.get(columnAIndex).getValueType());
                    }
                    catch(Exception e)
                    {
                    }
                }
            }
        }
        return tFilter;
    }
	private static List<TFilter> getTFilter(Object whereObj, MDataColumn mdc)
    {
        List<TFilter> tFilterList = new ArrayList<TFilter>();
        String whereStr = SqlFormat.getFieldRowSql(whereObj);
        whereStr = SqlCreate.formatWhere(whereStr, mdc, DBType.NONE, false);
        String lowerWhere = whereStr.toLowerCase();
        String andSign = " and ";
        String orSign = " or ";
        int andIndex = indexOf(lowerWhere, andSign, 0);// lowerWhere.IndexOf(andSign);
        int orIndex = indexOf(lowerWhere, orSign, 0);

        TFilter filter = null;
        if (andIndex == -1 && orIndex == -1)//仅有一个条件
        {
            filter = getSingleTFilter(whereStr, mdc);
            if (filter != null)
            {
                tFilterList.add(filter);
            }
        }
        else if (orIndex == -1) // 只有and条件
        {
            int andStartIndex = 0;
            while (andIndex > -1)
            {
                filter = getSingleTFilter(whereStr.substring(andStartIndex, andIndex - andStartIndex), mdc);
                if (filter != null)
                {
                    if (andStartIndex > 0)
                    {
                        filter.ao = Ao.And;
                    }
                    tFilterList.add(filter);
                }
                andStartIndex = andIndex + andSign.length();
                andIndex = indexOf(lowerWhere, andSign, andStartIndex + 1);
            }
            filter = getSingleTFilter(whereStr.substring(andStartIndex), mdc);
            if (filter != null)
            {
                filter.ao = Ao.And;
                tFilterList.add(filter);
            }

        }
        else if (andIndex == -1) //只有or 条件
        {
            int orStartIndex = 0;
            while (orIndex > -1)
            {
                filter = getSingleTFilter(whereStr.substring(orStartIndex, orIndex - orStartIndex), mdc);
                if (filter != null)
                {
                    if (orStartIndex > 0)
                    {
                        filter.ao = Ao.Or;
                    }
                    tFilterList.add(filter);
                }
                orStartIndex = orIndex + orSign.length();
                orIndex = indexOf(lowerWhere, orSign, orStartIndex + 1);
            }
            filter = getSingleTFilter(whereStr.substring(orStartIndex), mdc);
            if (filter != null)
            {
                filter.ao = Ao.Or;
                tFilterList.add(filter);
            }
        }
        else //有and 又有 or
        {
            boolean isAnd = andIndex < orIndex;
            boolean lastAnd = isAnd;
            int andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理

            int andOrStartIndex = 0;

            while (andOrIndex > -1)
            {
                filter = getSingleTFilter(whereStr.substring(andOrStartIndex, andOrIndex - andOrStartIndex), mdc);
                if (filter != null)
                {
                    if (andOrStartIndex > 0)
                    {
                        filter.ao = lastAnd ? Ao.And : Ao.Or;
                    }
                    tFilterList.add(filter);
                }
                andOrStartIndex = andOrIndex + (isAnd ? andSign.length() : orSign.length());
                if (isAnd)
                {
                    andIndex = indexOf(lowerWhere, andSign, andOrStartIndex + 1);
                }
                else
                {
                    orIndex = indexOf(lowerWhere, orSign, andOrStartIndex + 1);
                }
                lastAnd = isAnd;
                if (andIndex == -1)
                {
                    isAnd = false;
                    andOrIndex = orIndex;
                }
                else if (orIndex == -1)
                {
                    isAnd = true;
                    andOrIndex = andIndex;
                }
                else
                {
                    isAnd = andIndex < orIndex;
                    andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
                }

            }
            filter = getSingleTFilter(whereStr.substring(andOrStartIndex), mdc);
            if (filter != null)
            {
                filter.ao = lastAnd ? Ao.And : Ao.Or;
                tFilterList.add(filter);
            }
        }
        // String firstFilter=whereStr.su


        return tFilterList;
    }
	
	private static boolean compareMore(MDataRow row, List<TFilter> filters)
    {
        if (row == null) { return false; }
        boolean result = false;

        MDataCell cell = null, otherCell = null;
        int sqlDbType = Types.INTEGER;
        //单个条件比较的结果
        boolean moreResult = false;
        Object valueA = null, valueB = null;
        for (TFilter item : filters)
        {
            if (item.op == Op.None)
            {
                moreResult = true;
            }
            else
            {
                if (item.columnAIndex == -1)
                {
                    valueA = item.valueA;
                    sqlDbType = Types.NVARCHAR;
                }
                else
                {
                    cell = row.get(item.columnAIndex);
                    valueA = cell.getValue();
                    sqlDbType = cell.getCellstruct().getSqlType();
                }
                if (item.columnBIndex > -1)
                {
                    otherCell = row.get(item.columnBIndex);
                    valueB = otherCell.getValue();
                    if (DataType.getGroup(sqlDbType) != DataType.getGroup(otherCell.getCellstruct().getSqlType()))
                    {
                        sqlDbType = Types.NVARCHAR;//不同类型的比较，转成字符串比较。
                    }
                }
                else
                {
                    valueB = item.valueB;
                }
                switch (item.op)
                {
                    case IsNull:
                        moreResult = cell != null && cell.isNull();
                        break;
                    case IsNotNull:
                        moreResult = cell != null && !cell.isNull();
                        break;
                    default:
                        if (String.valueOf(valueA) == "" || String.valueOf(valueB) == "")//空格的问题，稍后回来处理。
                        {
                            int a = valueA == null ? 1 : (String.valueOf(valueA) == "" ? 2 : 3);
                            int b = valueB == null ? 1 : (String.valueOf(valueB) == "" ? 2 : 3);
                            switch (item.op)
                            {
                                case Big:
                                    moreResult = a > b;
                                    break;
                                case Like:
                                case Equal:
                                    moreResult = a == b;
                                    break;
                                case SmallEqual:
                                    moreResult = a <= b;
                                    break;
                                case BigEqual:
                                    moreResult = a >= b;
                                    break;
                                case Small:
                                    moreResult = a < b;
                                    break;
                                case NotLike:
                                case NotEqual:
                                    moreResult = a != b;
                                    break;
                                //case Op.In:

                                //    break;
                                //case Op.NotIn:
                                //    break;
                            }
                        }
                        else
                        {
                            moreResult = compare(sqlDbType, valueA, item.op, valueB);
                        }
                        break;
                }

            }
            switch (item.ao)
            {
                case And:
                    result = result && moreResult;
                    break;
                case Or:
                    result = result || moreResult;
                    break;
                default:
                    result = moreResult;
                    break;
            }
        }

        return result;
    }
	private static boolean compare(int sqlType, Object valueA, Op op, Object valueB)
    {
        try
        {
            switch (op)
            {
                case Big:
                case BigEqual:
                    switch (DataType.getGroup(sqlType))
                    {
                        case 1://int
                            return op == Op.Big ? (Long)KnCommonUtils.changeType(valueA, Long.class)  > (Long)KnCommonUtils.changeType(valueB,Long.class) : (Long)KnCommonUtils.changeType(valueA,Long.class) >= (Long)KnCommonUtils.changeType(valueB,Long.class);
                        case 2://datetime
                            return op == Op.Big ? ((Date)KnCommonUtils.changeType(valueA,Date.class)).getTime() > ((Date)KnCommonUtils.changeType(valueB,Date.class)).getTime() : ((Date)KnCommonUtils.changeType(valueA,Date.class)).getTime() >= ((Date)KnCommonUtils.changeType(valueB,Date.class)).getTime();
                        default:
                            int value = valueA.toString().compareTo(valueB.toString());
                            return op == Op.Big ? value == 1 : value > -1;
                    }

                case Equal:
                    return StringUtils.stripEnd(valueA.toString(), " ").compareTo(StringUtils.stripEnd(valueB.toString(), " ")) == 0;
                case NotEqual:
                    return StringUtils.stripEnd(valueA.toString(), " ").compareTo(StringUtils.stripEnd(valueB.toString(), " ")) != 0;
                case Small:
                case SmallEqual:
                    switch (DataType.getGroup(sqlType))
                    {
                        case 1://int
                            return op == Op.Small ? (Long)KnCommonUtils.changeType(valueA, Long.class) < (Long)KnCommonUtils.changeType(valueB,Long.class) : (Long)KnCommonUtils.changeType(valueA, Long.class) <= (Long)KnCommonUtils.changeType(valueB,Long.class);
                        case 2://datetime
                            return op == Op.Small ? ((Date)KnCommonUtils.changeType(valueA,Date.class)).getTime() < ((Date)KnCommonUtils.changeType(valueB,Date.class)).getTime() : ((Date)KnCommonUtils.changeType(valueA,Date.class)).getTime() <= ((Date)KnCommonUtils.changeType(valueB,Date.class)).getTime();
                        default:
                            int value = String.valueOf(valueA).compareTo(valueB.toString());
                            return op == Op.Small ? value == -1 : value <= 0;
                    }
                case Like:
                case NotLike:
                    boolean result = false;
                    String bValue = String.valueOf(valueB);
                    if (!bValue.startsWith("%"))
                    {
                        result =StringUtils.startsWithIgnoreCase(String.valueOf(valueA), StringUtils.strip(bValue, "%"));
                    }
                    else if (!bValue.endsWith("%")) //'123    ' like '123%'
                    {
                    	result = StringUtils.endsWithIgnoreCase(StringUtils.stripEnd(String.valueOf(valueA), " "), StringUtils.strip(bValue, "%"));
                    }
                    else
                    {
                        result =StringUtils.indexOfIgnoreCase(String.valueOf(valueA), StringUtils.strip(bValue, "%")) > -1;
                    }
                    return op == Op.Like ? result : !result;
                case In:
                    return String.valueOf(valueB).contains(',' + String.valueOf(valueA) + ",");
                case NotIn:
                    return !String.valueOf(valueB).contains(',' + String.valueOf(valueA) + ",");
            }
            return false;
        }
        catch(Exception e)
        {
            return false;
        }
    }
	
    private static String[] splitWhereOrderby(String where, String orderby)
    {
        orderby = StringUtils.EMPTY;
        if (StringUtils.isNoneEmpty(where))
        {
            int orderbyIndex =StringUtils.indexOfIgnoreCase(where, "order by");
            if (orderbyIndex > -1)
            {
                orderby = where.substring(orderbyIndex);
                if (orderbyIndex > 0)
                {
                    where = where.substring(0, orderbyIndex - 1);//-1是去掉空格
                }
                else
                {
                    where = StringUtils.EMPTY;
                }
            }
        }
        return new String[]{where,orderby};
    }
    
    private static void filterPager(MDataRowCollection rows, int pageIndex, int pageSize)
    {
        if (pageIndex > -1 && pageSize > 0) //分页处理返回
        {
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            int start = (pageIndex - 1) * pageSize;//从第几条开始查(索引)
            int end = start + pageSize;//查到第N条结束(索引)。
            int rowCount = rows.size();
            if (rowCount > end)//总数>N
            {
                //rows.re
                rows.removeRange(end, rowCount - end);//移除尾数
            }
            if (start > 0)//总数>起即
            {
                if (rowCount > start)
                {
                    rows.removeRange(0, start);//移除起始数
                }
                else if (rowCount < start) //查询的超出总数
                {
                    rows.clear();//直接清空返回
                }
            }
        }
    }
}

class TFilter{
    Ao ao;
    Object valueA;
    int columnAIndex = -1;
    Op op;
    Object valueB;
    int columnBIndex = -1;
    public TFilter(Ao ao, Object valueA, int columnAIndex, Op op, Object valueB, int columnBIndex)
    {
        this.ao = ao;
        this.valueA = valueA;
        this.columnAIndex = columnAIndex;
        this.op = op;
        this.valueB = valueB;
        this.columnBIndex = columnBIndex;
    }
}

enum Ao
{
    None,
    And,
    Or
}

enum Op
{
    /// <summary>
    /// 没有操作
    /// </summary>
    None,
    /// <summary>
    /// 操作符号:">"
    /// </summary>
    Big,
    /// <summary>
    /// 操作符号:">="
    /// </summary>
    BigEqual,
    /// <summary>
    /// 操作符号:"="
    /// </summary>
    Equal,
    /// <summary>
    /// 操作符号:"&lt;>"
    /// </summary>
    NotEqual,
    /// <summary>
    /// 操作符号:"&lt;"
    /// </summary>
    Small,
    /// <summary>
    /// 操作符号:"&lt;="
    /// </summary>
    SmallEqual,
    /// <summary>
    /// 操作符号:"like"
    /// </summary>
    Like,
    /// <summary>
    /// 操作符号:"not like"
    /// </summary>
    NotLike,
    /// <summary>
    /// 是否Null值
    /// </summary>
    IsNull,
    /// <summary>
    /// 非Null值
    /// </summary>
    IsNotNull,
    /// <summary>
    /// 操作符号：In
    /// </summary>
    In,
    /// <summary>
    /// 操作符号：Not In
    /// </summary>
    NotIn

}